import os
import xlrd
import sys
import django
import datetime
from xlrd import xldate_as_tuple

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")
django.setup()
from apps.product.models import Product
from apps.order.models import BaseOrder, PriceUpdate
from apps.sales_man.models import Single, ApplicantList
from core.tools.mysql_connect import SqlModel

"""
首先通过SalesSign找到推荐人
然后将推荐人和数据放到BaseOrder中
"""


class InsertOrder(SqlModel):
    def __init__(self, p_id, return_name, tel, cash, return_time):
        super(InsertOrder, self).__init__()
        self.tel = tel
        self.p_id = p_id
        self.create_time = datetime.datetime.strptime(return_time, '%Y/%m/%d')
        self.create_time_str = self.create_time.strftime('%Y-%m-%d')
        self.return_name = return_name
        self.cash = cash
        self.bfb = 0
        self.f = tel.split("****")[0]
        self.l = tel.split("****")[1]

    def select_apply(self):
        d = {"f": self.f, "l": self.l}
        sql = "SELECT * FROM `sales_man_applicantlist` where " \
              "`tel` REGEXP '^{f}' and `tel` REGEXP '{l}$'".format(**d)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()  # 获取查询的所有记录
        apply_id_list = []
        for i in results:
            apply_id = i[0]
            if apply_id not in apply_id_list:
                apply_id_list.append(apply_id)
        return apply_id_list

    def select_sign(self, apply_id_list):
        base_info = Single.objects.filter(applicant_id__in=apply_id_list, product_id=self.p_id)
        info = base_info.distinct().values("t_user")
        if len(info) == 1:
            return base_info.order_by('-create_time').first()
        else:
            raise Exception("value error")

    def save_order(self, single):
        BaseOrder.objects.insert_order(single, self.create_time)


def insert():
    p_id = 9
    data = xlrd.open_workbook("minshengpuka.xlsx")  # 打开excel
    table = data.sheets()[0]
    rows = table.nrows  # 行数
    for i in range(1, rows):
        row_values = table.row_values(i)  # 某一行数据
        return_name = row_values[0]  # 1、姓名
        tel = row_values[1]  # 手机号
        # return_time = row_values[2]  # 时间
        return_time = datetime.datetime(*xldate_as_tuple(row_values[2], 0)).strftime('%Y/%m/%d')
        cash = row_values[3]
        insert_order = InsertOrder(p_id, return_name, tel, cash, return_time)
        apply_id_list = insert_order.select_apply()
        single = insert_order.select_sign(apply_id_list)
        insert_order.save_order(single)


if __name__ == '__main__':
    insert()
    pass
